﻿
CREATE FUNCTION dbo.credit_RatingAverage
	(
	@ApplID int,
	@CoApplID int = -1, -- get it from DB
	@MonthsBack tinyint = 12
	)
RETURNS numeric(10,2) -- percentage of "BAD" accounts
AS
BEGIN

DECLARE @DatePulled datetime
SELECT @DatePulled = DatePulled FROM t_CreditInfo WHERE (ApplicantID = @ApplID)

RETURN(

SELECT SUM(CONVERT(numeric(10, 2), MosPastDue) * _HighCreditAmount) / SUM(_HighCreditAmount) 
FROM  (SELECT (ISNULL(RATES.MosPastDue, 0) + --(ISNUMERIC(SUBSTRING(v_CreditLiability._PAYMENT_PATTERN_Data, 1, 1)) ^ 1) *
               (v_CreditLiability._CURRENT_RATING_Code - 1)) / NULLIF (dbo.MinNum(v_CreditLiability._MonthsReviewedCount, @MonthsBack), 0) AS MosPastDue, 
                      v_CreditLiability._HighCreditAmount
       FROM  credit_PastDues(@ApplID,DEFAULT,@MonthsBack) RATES RIGHT OUTER JOIN
                      v_CreditLiability ON RATES.CreditLiabilityID = v_CreditLiability.ID
       WHERE v_CreditLiability.ApplicantID = @ApplID AND
						 v_CreditLiability._HighCreditAmount > 0 AND
						 v_CreditLiability._CURRENT_RATING_Code > 0
			) RATE_HC
/*
SELECT AVG(CONVERT(real, ISNULL(RATES.Rate, v_CreditLiability._CURRENT_RATING_Code - 1)))
FROM  (SELECT CreditLiabilityID, SUM(Code) AS Rate
               FROM   t_1234s
               WHERE (Date > DATEADD(mm, - @MonthsBack, @DatePulled))
               GROUP BY CreditLiabilityID) RATES RIGHT OUTER JOIN
               v_CreditLiability ON RATES.CreditLiabilityID = v_CreditLiability.ID
WHERE (v_CreditLiability._CURRENT_RATING_Code > 0)
*/
)

END

